package com.guochenglang.www.util.connect;

import java.lang.reflect.Field;

/**
 * sql构建器
 *
 * @author 郭程朗
 */
public class SqlBuilder {
    public SqlBuilder() {
    }

    /**
     * 插入语句的sql语句构建
     *
     * @param table   表名
     * @param comfort 插入的信息
     */
    public static String insertSql(String table, Object comfort) throws IllegalAccessException {
        //构建sql语句
        StringBuilder sql = new StringBuilder("INSERT INTO ");
        //插入目标表
        int count = 1;
        sql.append(table).append("(");
        Class<?> aClass = comfort.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        int length = length(comfort);
        /*
         * 在计数器没有到达数组长度时，添加字段“'name' ,”
         * 在计数器到达集合长度后,添加字段“'name' ) VALUES (”
         *
         *
         * */
        //通过集合长度判断设定多少个占位符'?'
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            String name = determindName(declaredField.getName());
            Object value = declaredField.get(comfort);
            if (value == null) {
                continue;
            }
            if (count == length) {
                sql.append(name).append(") VALUES(");
                break;
            }
            sql.append(name).append(",");
            count++;

        }
        for (int i = 1; i <= count; i++) {
            if (i == count) {
                sql.append("?)");
                break;
            }
            sql.append("?,");
        }
        return String.valueOf(sql);

    }

    /**
     * 删除语句的sql语句构建
     *
     * @param table      表名
     * @param conditions 执行的条件
     */
    public static String deleteSql(String table, Object conditions) throws IllegalAccessException {
        //构建sql语句
        StringBuilder sql = new StringBuilder("DELETE FROM ");
        sql.append(table).append(" WHERE (");
        Class<?> aClass = conditions.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        int count = 1;
        int length = length(conditions);
        /*
         * 在计数器没有到达长度时，添加字符“'name'=? &&”
         * 在计数器到达长度后，添加字符“'name' = ? )”
         *
         * */

        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            String name = determindName(declaredField.getName());
            Object value = declaredField.get(conditions);
            if (value == null) {
                continue;
            }
            if (count == length) {
                sql.append(name).append("= ?)");
                break;
            }
            sql.append(name).append("= ? && ");
            count++;

        }
        return String.valueOf(sql);
    }

    /**
     * 修改语句的sql语句构建
     *
     * @param table      表名
     * @param conditions 执行的条件
     * @param set        修改的数据
     */
    public static String updateSql(String table, Object set, Object conditions) throws IllegalAccessException {
        //构建sql语句
        StringBuilder sql = new StringBuilder("UPDATE ");
        sql.append(table).append(" SET ");
        int count = 1;
        Class<?> aClass = set.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //获取有效字符长度
        int length = length(set);
        /*
            在到达集合长度前，添加字符“'name '= ?,”
            到达集合长度后，添加"'name '=? WHERE ("
         *
          */
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            String name = determindName(declaredField.getName());
            Object value = declaredField.get(set);
            if (value == null) {
                continue;
            }
            if (count == length) {
                sql.append(name).append(" =? ").append("WHERE (");
                break;
            }
            sql.append(name).append(" = ?, ");
            count++;
        }
        /*
         * 到达集合长度前，添加字符“'key' = ? && ”
         * 到达集合长度后，添加字符“'key '=?)”
         * */
        count = 1;
        Class<?> aClass1 = conditions.getClass();
        Field[] declaredFields1 = aClass1.getDeclaredFields();
        length = length(conditions);
        for (Field field : declaredFields1) {
            field.setAccessible(true);
            String name = determindName(field.getName());
            Object value = field.get(conditions);
            if (value == null) {
                continue;
            }
            if (count == length) {
                sql.append(name).append("= ? )");
                break;
            }
            sql.append(name).append(" = ? && ");
            count++;
        }
        return String.valueOf(sql);
    }


    /**
     * 搜索sql语句构建
     *
     * @param table 表名
     * @param conditions 条件
     * @return 搜索的sql语句
     * @throws IllegalAccessException 非法访问异常
     */
    public static String selectSqlMax(String table, Object conditions) throws IllegalAccessException {
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        //sql语句的构建
        //sql语句占位符的构建
        sql.append(table);
        if (conditions != null) {
            Class<?> aClass = conditions.getClass();
            int count = 1;
            boolean isFirst = true;
            Field[] declaredFields = aClass.getDeclaredFields();
            //获取有效字符长度
            int length = length(conditions);
            //sql语句的构建
            /*
             * 到达有效字符长度前插入name= ? &&
             * 到达有效字符长度后插入= ?)
             * */
            for (Field declaredField : declaredFields) {
                declaredField.setAccessible(true);
                String name = determindName(declaredField.getName());
                Object value = declaredField.get(conditions);
                if (value == null) {
                    continue;
                }
                if (isFirst) {
                    sql.append(" WHERE (");
                    isFirst = false;
                }
                if (count == length) {
                    sql.append(name).append("= ?)");
                    break;
                }
                sql.append(name).append("= ? && ");
                count++;
            }
        }
        return String.valueOf(sql);
    }

    /**
     * 分页搜索
     *
     * @param table      表名
     * @param page       第n页
     * @param num        每页显示的条目数
     * @param conditions 查询条件
     * @param order      排序根据的字段
     * @param mode       排序方式
     * @return sql语句
     * @throws IllegalAccessException 非法访问异常
     */
    public static String selectByLimit(String table, int page, int num, Object conditions, String order, String mode) throws IllegalAccessException {
        String oldSql = selectSqlMax(table, conditions);
        StringBuilder sql = new StringBuilder(oldSql);
        //在原先的搜索字符串下再添加ORDER BY
        sql.append(" ORDER BY ").append(determindName(order)).append(" ").append(mode).append(" LIMIT ").append((page - 1) * num).append(",").append(num).append("");
        return String.valueOf(sql);
    }

    /**
     * java名转为mysql的名字
     *
     * @param name 判断的字符
     * @return 转换后的字符
     */
    public static String determindName(String name) {
        boolean isReturn = true;
        int index = 0;
        for (index = 0; index < name.length(); index++) {
            //搜索到大写字母
            if (name.charAt(index) >= 'A' && name.charAt(index) <= 'Z') {
                isReturn = false;
                break;
            }
        }
        if (isReturn) {
            //不需要转换，返回原来的字符串
            return name;
        } else {
            //将大写字母变成小写，并在两个单词中以‘_’连接
            char bigValue = (char) (name.charAt(index) + 'a' - 'A');
            return name.substring(0, index) + '_' + bigValue + name.substring(index + 1);
        }

    }

    /**
     * 获取有效数组的长度，即传入值不为空
     *
     * @param obj 传入的需要获取有效长度的类
     */
    private static int length(Object obj) throws IllegalAccessException {
        Class<?> aClass = obj.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //获得成员个数
        int length = declaredFields.length;
        //当成员值为null时，有效字符长度-1
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            String name = determindName(declaredField.getName());
            Object value = declaredField.get(obj);
            if (value == null || value == "") {
                length--;
            }
        }
        return length;
    }

}
